﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_DataFileSize]
#-- Purpose:		Maintains the amount of free space that needs to be 
#--					retained for controlled file growth and reduced 
#--					fragmentation
#--	Last Update:	07/14/2016
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_DataFileSize]
(
	@iIncrement				int		= 50, 
	@iMaxFullPct			int		= 90,
	@min_disk_free_pct		int		= 15,
	@debug_only				bit		= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@database_name sysname, @sSQL varchar(MAX), @vlf_optimal int, @vlf_minimal int,
			@pct_log_total float, @pct_log_used float, @TemplateLogGrowth nvarchar(MAX), @major int
DECLARE		@diskspace TABLE (dbd_drive nvarchar(256), dbd_capacityMB varchar(256), dbd_usedMB varchar(256), dbd_freeMB varchar(256), dbd_free_pct varchar(6))
DECLARE		@file_size TABLE (database_id int, Fileid int, FileGroup int, TotalExtents int, UsedExtents int, name sysname, physical_name nchar(520))
DECLARE		@progname sysname
DECLARE	@results TABLE
(
	database_id				int,
	database_name			sysname,
	Fileid					int,
	FileGroup				int,
	total_MB				decimal(18,2),
	used_MB					decimal(18,2),
	free_MB					decimal(18,2),
	newSize_MB				bigint,
	name					sysname,
	physical_name			nvarchar(2000)
)
DECLARE @tAOAGStat TABLE ([database_id] int, [database_name] nvarchar(256))

--- Define the name of this program (for logs)
SELECT	@progname = 'spMaint_DataFileSize',
		@TemplateLogGrowth = 'ALTER DATABASE [VAR_DBNAME] MODIFY FILE ( NAME = N''[VAR_FILENAME]'', FILEGROWTH = [VAR_SIZEMB]MB )',
		@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int)

--- Determine if this is an AlwaysOn setup - and if so which databases are in AlwaysOn and this server is the primary.
IF @major > 10
  BEGIN
	IF OBJECT_ID('[sys].[dm_hadr_availability_group_states]') IS NOT NULL
		SELECT @sSQL = 'SELECT		t2.[database_id],
			t2.[name] [database_name]
FROM		[sys].[availability_replicas] t1
JOIN		[sys].[databases] t2 ON t1.[replica_id] = t2.[replica_id]
JOIN		[sys].[availability_groups] t3 ON t1.[group_id] = t3.[group_id]
LEFT JOIN	[sys].[dm_hadr_availability_group_states] t4 ON t1.[group_id] = t4.[group_id]
WHERE		t1.[replica_server_name] <> t4.[primary_replica]'
	ELSE
		SELECT	@sSQL = 'SELECT 0, SPACE(0) WHERE 1 = 0'

	INSERT INTO @tAOAGStat EXEC (@sSQL)
  END

--- Get Current Disk Information
INSERT INTO @diskspace (dbd_drive, dbd_capacityMB, dbd_usedMB, dbd_freeMB, dbd_free_pct) EXEC [dbo].[spIO_GetVolumeSpace] ''

--- Retrieve the size of the data files and database log file info
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
	SELECT		db.name
	FROM		[master].[sys].[databases] db
	LEFT JOIN	@tAOAGStat t2 ON db.[name] = t2.[database_name]
	WHERE		db.name NOT IN ('tempdb', 'ReportServerTempDB')
				AND db.name NOT IN (SELECT database_name FROM [dbo].[vwMaint_DoNotMaintain])
				AND state = 0
				AND is_read_only = 0
				AND source_database_id IS NULL  -- Cannot change file sizes on Snapshots
				AND t2.[database_name] IS NULL
	ORDER BY	db.database_id
OPEN crs
FETCH NEXT FROM crs INTO @database_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Retrieve the size of the data files
	SELECT	@sSQL = 'USE ' + QUOTENAME(@database_name) + '; DBCC SHOWFILESTATS WITH NO_INFOMSGS;'
	INSERT INTO @file_size (Fileid, FileGroup, TotalExtents, UsedExtents, name, physical_name) 
		EXEC (@sSQL)
	UPDATE @file_size SET database_id = DB_ID(@database_name) WHERE database_id IS NULL
	
	FETCH NEXT FROM crs INTO @database_name
  END
CLOSE crs
DEALLOCATE crs

--- Get the list of files that need to grow
;WITH data_size AS
(
	SELECT		t1.database_id,
				DB_NAME(t1.database_id) database_name,
				t1.Fileid,
				t1.FileGroup,
				((t1.TotalExtents * 8.00 / 1024) * (mf.size / t1.TotalExtents)) TotalMB,
				((t1.UsedExtents * 8.00 / 1024) * (mf.size / t1.TotalExtents)) UsedMB,
				(((t1.TotalExtents - t1.UsedExtents) * 8.00 / 1024) * (mf.size / t1.TotalExtents)) FreeMB,
				t1.name,
				t1.physical_name
	FROM		[master].[sys].[master_files] mf
	JOIN		@file_size t1 ON mf.name = t1.name
				AND mf.physical_name = t1.physical_name
)
INSERT INTO @results
	SELECT		database_id,
				database_name,
				Fileid,
				FileGroup,
				TotalMB,
				UsedMB,
				FreeMB,
				(CASE	WHEN UsedMB < 40 THEN 50
						ELSE (CAST(LEFT(CAST(CAST((UsedMB / 0.8) as int) as varchar(1000)), LEN(CAST(CAST((UsedMB / 0.8) as int) as varchar(1000))) - 2) as int) + 1) * 100
						END) newTotalMB,
				name,
				physical_name
	FROM		data_size
	WHERE		(UsedMB / (TotalMB * 1.00)) >= (@iMaxFullPct / 100.00)

--- If debug_only, display the files needing growth
IF @debug_only = 1
  BEGIN
	SELECT		t2.dbd_capacityMB,
				t2.dbd_freeMB,
				t2.growth_MB,
				t1.*
	FROM		@results t1
	JOIN		(
				SELECT		dbd.dbd_drive,
							dbd.dbd_capacityMB,
							dbd.dbd_usedMB,
							dbd.dbd_freeMB,
							dbd.dbd_free_pct,
							SUM(newSize_MB - total_MB) growth_MB
				FROM		@results res
				JOIN		@diskspace dbd ON res.physical_name LIKE dbd.dbd_drive + '%'
				WHERE		(res.used_MB / (res.total_MB * 1.00)) >= (@iMaxFullPct / 100.00)
				GROUP BY	dbd.dbd_drive,
							dbd.dbd_capacityMB,
							dbd.dbd_usedMB,
							dbd.dbd_freeMB,
							dbd.dbd_free_pct
				) t2 ON t1.physical_name LIKE t2.dbd_drive + '%'
  END

--- Make the Changes to data size
DECLARE crs CURSOR FOR
	SELECT		'ALTER DATABASE ' + QUOTENAME(t1.database_name) + ' MODIFY FILE ( NAME = N''' + t1.name + ''', SIZE = ' + CAST(t1.newSize_MB as varchar(1000)) + 'MB )'
	FROM		@results t1
	JOIN		(
				SELECT		dbd.dbd_drive,
							dbd.dbd_capacityMB,
							dbd.dbd_usedMB,
							dbd.dbd_freeMB,
							dbd.dbd_free_pct,
							SUM(newSize_MB - total_MB) growth_MB
				FROM		@results res
				JOIN		@diskspace dbd ON res.physical_name LIKE dbd.dbd_drive + '%'
				WHERE		(res.used_MB / (res.total_MB * 1.00)) >= (@iMaxFullPct / 100.00)
				GROUP BY	dbd.dbd_drive,
							dbd.dbd_capacityMB,
							dbd.dbd_usedMB,
							dbd.dbd_freeMB,
							dbd.dbd_free_pct
				) t2 ON t1.physical_name LIKE t2.dbd_drive + '%'
OPEN crs
FETCH NEXT FROM crs INTO @sSQL
WHILE @@FETCH_STATUS <> -1
  BEGIN
	IF @debug_only = 1
		PRINT @sSQL
	ELSE
	  BEGIN
		BEGIN TRY
			EXEC (@sSQL)
		END TRY
		BEGIN CATCH
			PRINT SPACE(0)
		END CATCH
		
		--- Log to the SQL Server error log
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
	  END
	  
	FETCH NEXT FROM crs INTO @sSQL
  END
CLOSE crs
DEALLOCATE crs
	
--- Process changes to the increment of growth
DECLARE crsLogGrowth CURSOR FOR
	SELECT		REPLACE(
					REPLACE(
						REPLACE(@TemplateLogGrowth, '[VAR_DBNAME]', QUOTENAME(DB_NAME(mf.[database_id])))
					, '[VAR_FILENAME]', mf.[name])
				, '[VAR_SIZEMB]', CAST(
							(CASE	WHEN (mf.[size] * 8.00 / 1024) < 10 THEN 1
									WHEN (mf.[size] * 8.00 / 1024) < 1000 THEN 10
									ELSE 100
									END) as varchar(4000)))
	FROM		[master].[sys].[databases] db
	JOIN		[master].[sys].[master_files] mf ON db.[database_id] = mf.[database_id]
	LEFT JOIN	@tAOAGStat t2 ON db.[name] = t2.[database_name]
	WHERE		db.name NOT IN ('tempdb', 'ReportServerTempDB')
				AND db.name NOT IN (SELECT database_name FROM [dbo].[vwMaint_DoNotMaintain])
				AND db.state = 0
				AND db.is_read_only = 0
				AND db.source_database_id IS NULL  -- Cannot change file sizes on Snapshots
				AND (
					mf.[is_percent_growth] = 1
					OR (mf.[growth] * 8.00 / 1024) <> 
							(CASE	WHEN (mf.[size] * 8.00 / 1024) < 10 THEN 1
									WHEN (mf.[size] * 8.00 / 1024) < 1000 THEN 10
									ELSE 100
									END)
				)
				AND t2.[database_name] IS NULL
OPEN crsLogGrowth
FETCH NEXT FROM crsLogGrowth INTO @sSQL
WHILE @@FETCH_STATUS <> -1
  BEGIN
	IF @debug_only = 1
		PRINT	@sSQL
	ELSE
	  BEGIN
		BEGIN TRY
			EXEC (@sSQL)
		END TRY
		BEGIN CATCH
			PRINT SPACE(0)
		END CATCH

		--- Log to the SQL Server error log
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
	  END

	FETCH NEXT FROM crsLogGrowth INTO @sSQL
  END
CLOSE crsLogGrowth
DEALLOCATE crsLogGrowth
	
SET NOCOUNT OFF